/**
 * 
 */
package com.base.myproject.client.busi.service.assessment;

import java.util.ArrayList;
import java.util.List;

import com.base.myproject.client.busi.BaseStore;
import com.base.myproject.client.busi.BO.BusiObject;
import com.base.myproject.client.jdbc.StoreInfo;
import com.base.myproject.client.tools.Debug;
import com.base.myproject.client.ui.BaseGrid;
import com.base.myproject.client.ui.BaseTabPanel;
import com.base.myproject.client.ui.celleditor.BaseSimpleCombox;
import com.base.myproject.client.ui.celleditor.CellEditorFactory;
import com.extjs.gxt.ui.client.Style.SelectionMode;
import com.extjs.gxt.ui.client.data.ModelType;
import com.extjs.gxt.ui.client.event.ButtonEvent;
import com.extjs.gxt.ui.client.event.Events;
import com.extjs.gxt.ui.client.event.GridEvent;
import com.extjs.gxt.ui.client.event.Listener;
import com.extjs.gxt.ui.client.event.SelectionListener;
import com.extjs.gxt.ui.client.widget.ContentPanel;
import com.extjs.gxt.ui.client.widget.HorizontalPanel;
import com.extjs.gxt.ui.client.widget.Html;
import com.extjs.gxt.ui.client.widget.MessageBox;
import com.extjs.gxt.ui.client.widget.VerticalPanel;
import com.extjs.gxt.ui.client.widget.button.Button;
import com.extjs.gxt.ui.client.widget.form.FieldSet;
import com.extjs.gxt.ui.client.widget.form.SimpleComboBox;
import com.extjs.gxt.ui.client.widget.grid.CheckBoxSelectionModel;
import com.extjs.gxt.ui.client.widget.grid.ColumnConfig;
import com.extjs.gxt.ui.client.widget.grid.ColumnModel;
import com.extjs.gxt.ui.client.widget.layout.ColumnLayout;
import com.google.gwt.user.client.ui.Label;
import com.google.gwt.user.client.ui.Widget;

/**
 * 
 * 考核，美容美发系统的绩效考核。
 * 思路：根据考核办法，选择的考核人员，进行考核
 * 可以保存考核办法。
 * @author Administrator
 *2010-1-27
 */
public class AssessmentPanel  extends ContentPanel{
	BaseStore<BusiObject> store;
	BaseStore<BusiObject> bcodestore;
	BaseStore<BusiObject> assessment_bcode_tbl_g_store;
	
	CellEditorFactory celleditorfactory = new CellEditorFactory();
	SimpleComboBox<String> year;
	SimpleComboBox<String> month;
	Button add= new Button("添加>>");
	BaseSimpleCombox bsc1 =new BaseSimpleCombox("name","dictionary"," where type = '9001' ");
	BaseGrid<BusiObject>  bcodegrid ;
	public AssessmentPanel()
	{
		init();
	}
	
		
		void init() {
			this.setAutoHeight(true);
			year = celleditorfactory.getYEARCombobox();
			month = celleditorfactory.getMONTHCombobox();
			month.setAllowBlank(false);
			year.setAllowBlank(false);
			setWidth("760px");      
			setHeading("员工绩效查询");
			initstore();
			initassessment_bcode_tbl_gStore();
			initBcodeStore();
			initWidget();
			initListener();
		}
		
		void initWidget() {
			VerticalPanel vpanel = new VerticalPanel();
			
			
			HorizontalPanel hbuttonpanel = new HorizontalPanel();
			Button open = new Button("打开");
			Button save = new Button("保存");
			Button saveas = new Button("另存为");
			open.addSelectionListener(new SelectionListener<ButtonEvent>(){

				@Override
				public void componentSelected(ButtonEvent ce) {
					MessageBox.alert("警告", "DEMO版本禁止使用该功能！", null);
					
				}
				
			});
			save.addSelectionListener(new SelectionListener<ButtonEvent>(){

				@Override
				public void componentSelected(ButtonEvent ce) {
					MessageBox.alert("警告", "DEMO版本禁止使用该功能！", null);
					
				}
				
			});
			saveas.addSelectionListener(new SelectionListener<ButtonEvent>(){

				@Override
				public void componentSelected(ButtonEvent ce) {
					MessageBox.alert("警告", "DEMO版本禁止使用该功能！", null);
					
				}
				
			});
			hbuttonpanel.add(open);
			hbuttonpanel.add(save);
			hbuttonpanel.add(saveas);
			
			HorizontalPanel hpanel = new HorizontalPanel();
			VerticalPanel vpanel2 = new VerticalPanel();
			FieldSet fs1 = new FieldSet();
			fs1.setLayout(new ColumnLayout());
			fs1.add(new Label("请选择考核类型:"));
			fs1.add(bsc1.getCombox());
			fs1.setHeading("1  考核类型");
			FieldSet fs2 = new FieldSet();
			fs2.add(CreateBcodeGrid());
			
			fs2.setHeading("2  参与考核人员");
			
			vpanel2.add(fs1);
			vpanel2.add(fs2);
			
			
			FieldSet fs4 = new FieldSet();
			fs4.add(add);
			
			fs4.setHeading("3");
			
			
			hpanel.add(vpanel2);
			
			//hpanel.add(CreateBcodeGrid());
			hpanel.add(fs4);
			hpanel.setSpacing(20);
			
			FieldSet fs5 = new FieldSet();
			fs5.add(hbuttonpanel);
			
			fs5.setHeading("4");
			FieldSet fs6 = new FieldSet();
			fs6.add(CreateAssessment_bcode_tbl_gGrid());
			
			fs6.setHeading("5");
			
			VerticalPanel vpanel3 = new VerticalPanel();
			vpanel3.add(fs5);
			vpanel3.add(fs6);
			hpanel.add(vpanel3);
			
			hpanel.setMonitorWindowResize(true);
			
			vpanel.add(hpanel);
			
			vpanel.add(new Html("<br>"));
			vpanel.add(initQuery());
			vpanel.add(new Html("<br> 查询后，双击行可以看考核明细数据"));
			vpanel.add(CreateGrid());
			
			add(vpanel);
		}
		java.util.HashMap<String, Object> defaultvalue = new java.util.HashMap<String, Object>();
		void initListener()
		{
			add.addSelectionListener(new SelectionListener<ButtonEvent>(){

				@Override
				public void componentSelected(ButtonEvent ce) {
					
					for(BusiObject jmd :bcodegrid.getSelectionModel().getSelectedItems())
					{
						jmd.set("methodicode", bsc1.getValue());
						defaultvalue.put("methodicode", bsc1.getValue());
						defaultvalue.put("bcode", jmd.get("bcode"));
						assessment_bcode_tbl_g_store.setDefaultvalue(defaultvalue);
						assessment_bcode_tbl_g_store.addX();
					}
				}
				
			});
		}
		void initstore() {
			ModelType type = new ModelType();
			type.setRoot("records");
			type.setRecordName("record");
			type.addField("bcode");

			type.addField("assesstotal");
			
			StoreInfo info = new StoreInfo();
			
			info.setTablename("notable");
			info.setNeedAllRow("no");
			info.setForcenolimit("true");
			
		
			info.setSql(initreloadparam());
		
			store = new BaseStore<BusiObject>(type, info,false);
			
			
			
			
		}
		void initBcodeStore()
		{
			ModelType type = new ModelType();
			type.setRoot("records");
			type.setRecordName("record");
			type.addField("bcode");

			type.addField("bname");
			
			StoreInfo info = new StoreInfo();
			
			info.setTablename("bcode");
			info.setNeedAllRow("no");
			info.setForcenolimit("true");
			
		
			info.setSql("select bcode,bname from bcode where bcode is not null");
		
			bcodestore = new BaseStore<BusiObject>(type, info,true);
			
		}
		
		void initassessment_bcode_tbl_gStore()
		{
			ModelType type = new ModelType();
			type.setRoot("records");
			type.setRecordName("record");
			type.addField("bcode");

			type.addField("methodicode");
			type.addField("icode");
			
			StoreInfo info = new StoreInfo();
			
			info.setTablename("assessment_bcode_tbl_g");
			info.setNeedAllRow("no");
			info.setForcenolimit("true");
			
			info.setKeyname(new String[]{"icode"});
			info.setSql("select * from assessment_bcode_tbl_g  ");
		
			assessment_bcode_tbl_g_store = new BaseStore<BusiObject>(type, info,true);
			
		}
		
		
		
		Widget CreateBcodeGrid()
		{
			CheckBoxSelectionModel<BusiObject> cbs = new CheckBoxSelectionModel<BusiObject>();
			List<ColumnConfig> columns = new ArrayList<ColumnConfig>();
			columns.add(cbs.getColumn());
		
			columns.add(new ColumnConfig("bcode", "人员编码", 50));
			//columns.add(new ColumnConfig("email", "会员邮箱", 50));
			columns.add(new ColumnConfig("bname", "人员名称", 100));
			
			
			
			ColumnModel cm = new ColumnModel(columns);
			cbs.setSelectionMode(SelectionMode.MULTI);
			bcodegrid = new BaseGrid<BusiObject>(bcodestore, cm,false,false,null);
			bcodegrid.setSelectionModel(cbs);

			bcodegrid.setSize(250,300);
			bcodegrid.getView().setForceFit(true);
			return bcodegrid;
		}
		Widget CreateGrid()
		{
			CheckBoxSelectionModel<BusiObject> cbs = new CheckBoxSelectionModel<BusiObject>();
			List<ColumnConfig> columns = new ArrayList<ColumnConfig>();
			columns.add(cbs.getColumn());
		
			columns.add(new ColumnConfig("bcode", "名字", 50));
			//columns.add(new ColumnConfig("email", "会员邮箱", 50));
			columns.add(new ColumnConfig("assesstotal", "提成", 100));
			
			
			
			ColumnModel cm = new ColumnModel(columns);
			cbs.setSelectionMode(SelectionMode.SINGLE);
			BaseGrid<BusiObject>  grid = new BaseGrid<BusiObject>(store, cm,false,false,null);
//			grid.setSelectionModel(cbs);
//			grid
			grid.setSize(760,300);
			grid.addListener(Events.RowDoubleClick, new Listener<GridEvent>(){

				@Override
				public void handleEvent(GridEvent be) {
					Debug.printf("--"+be.getValue()+" "+be.getProperty()+" "+be.getRowIndex());
					Debug.printf("value="+((BaseGrid)be.getGrid()).getValue(be.getRowIndex(),"bcode"));
					//Debug.printf("value1="+((BaseGrid)be.getGrid()).getStore().getValue("bcode"));
					Debug.printf(year.getValue().get("value"));
					Debug.printf(month.getValue().get("value"));
					AssessmentQueryDetail aqd = new AssessmentQueryDetail(year.getValue().get("value").toString(),month.getValue().get("value").toString(),((BaseGrid)be.getGrid()).getValue(be.getRowIndex(),"bcode").toString());
					BaseTabPanel.getInstance().addTab(((BaseGrid)be.getGrid()).getValue(be.getRowIndex(),"bcode").toString()+"考核明细",aqd);
				}
				
			});
			grid.getView().setForceFit(true);
			return grid;
		}
		
		Widget CreateAssessment_bcode_tbl_gGrid()
		{
			CheckBoxSelectionModel<BusiObject> cbs = new CheckBoxSelectionModel<BusiObject>();
			List<ColumnConfig> columns = new ArrayList<ColumnConfig>();
			columns.add(cbs.getColumn());
		
			columns.add(new ColumnConfig("bcode", "名字", 50));
			//columns.add(new ColumnConfig("email", "会员邮箱", 50));
			columns.add(new ColumnConfig("methodicode", "提成办法", 100));
			
			
			
			ColumnModel cm = new ColumnModel(columns);
			cbs.setSelectionMode(SelectionMode.SINGLE);
			BaseGrid<BusiObject>  grid = new BaseGrid<BusiObject>(assessment_bcode_tbl_g_store, cm,true,true,null);
			grid.setSelectionModel(cbs);

			grid.setSize(250,300);
			grid.getView().setForceFit(true);
			return grid;
		}
		Widget initQuery()
		{
			HorizontalPanel hpanel = new HorizontalPanel();
			hpanel.add(new Label("考核 年："));
			
			hpanel.add(year);
			
			
			hpanel.add(new Label("月："));
		
			hpanel.add(month);
			
			Button query = new Button("查询");
			Button export = new Button("导出到excel");
			hpanel.add(query);
			hpanel.add(export);
			export.addSelectionListener(new SelectionListener<ButtonEvent>(){
				public void componentSelected(ButtonEvent ce) {
					MessageBox.alert("警告", "DEMO版本禁止使用该功能！", null);	
				}
			});
			query.addSelectionListener(new SelectionListener<ButtonEvent>(){

				@Override
				public void componentSelected(ButtonEvent ce) {
					Debug.printf(year.getValue().getValue());
					Debug.printf(month.getSimpleValue());
					if(year.isValid() && month.isValid())
					{
					StoreInfo info  = store.getStoreinfo();;
					info.setSql(initreloadparam());
					store.setStoreinfo(info);
					store.reload();
					}
					else
					{
						MessageBox.alert("警告", "请先录入 年，月", null);
					}
				}
				
			});
			return hpanel;
		}
		/**
		 * 此处的sql，被AssessmentqueryDetail 使用，如果此处修改，那么AssessmentqueryDetail的sql也要修改
		 * 保持数据的一致性
		 * 
		 * @return
		 */
		
		String initreloadparam()
		{
			java.lang.StringBuffer sbf = new StringBuffer();
			sbf.append(" select (select bname from bcode where bcode.bcode= z.bcode) as bcode,cast (sum(assessrate ) as money) as assesstotal");
			sbf.append("     ");
			sbf.append(" from");
			sbf.append(" (   ");
			sbf.append(" select a.bcode as bcode   ");
			sbf.append(" ,   ");
			sbf.append(" case");
			sbf.append(" when f.bcode is not null        ");
			sbf.append(" then");
			sbf.append(" 	case when f.methodse='实收比例'");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then f.method1 * realtotal ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then f.method2 * realtotal ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then f.method3 * realtotal ");
			sbf.append(" 	end");
			sbf.append(" 	when f.methodse='应收比例'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then f.method1 * total     ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then f.method2 * total     ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then f.method3 * total     ");
			sbf.append(" 	end");
			sbf.append(" 	when f.methodse='固定金额'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then f.method1             ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then f.method2             ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then f.method3             ");
			sbf.append(" 	end");
			sbf.append(" 	end");
			sbf.append(" when h.systemid is not null     ");
			sbf.append(" then");
			sbf.append(" 	case when h.methodse='实收比例'");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then h.method1 * realtotal ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then h.method2 * realtotal ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then h.method3 * realtotal ");
			sbf.append(" 	end");
			sbf.append(" 	when h.methodse='应收比例'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then h.method1 * total     ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then h.method2 * total     ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then h.method3 * total     ");
			sbf.append(" 	end");
			sbf.append(" 	when h.methodse='固定金额'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then h.method1             ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then h.method2             ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then h.method3             ");
			sbf.append(" 	end");
			sbf.append(" 	end");
			sbf.append(" when e.service is not null      ");
			sbf.append(" then");
			sbf.append(" 	case when e.methodse='实收比例'");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then e.method1 * realtotal ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then e.method2 * realtotal ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then e.method3 * realtotal ");
			sbf.append(" 	end");
			sbf.append(" 	when e.methodse='应收比例'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then e.method1 * total     ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then e.method2 * total     ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then e.method3 * total     ");
			sbf.append(" 	end");
			sbf.append(" 	when e.methodse='固定金额'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then e.method1             ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then e.method2             ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then e.method3             ");
			sbf.append(" 	end");
			sbf.append(" 	end");
			sbf.append(" when c.systemid is not null     ");
			sbf.append(" then");
			sbf.append(" 	case when c.methodse='实收比例'");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then c.method1 * realtotal ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then c.method2 * realtotal ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then c.method3 * realtotal ");
			sbf.append(" 	end");
			sbf.append(" 	when c.methodse='应收比例'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then c.method1 * total     ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then c.method2 * total     ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then c.method3 * total     ");
			sbf.append(" 	end");
			sbf.append(" 	when c.methodse='固定金额'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then c.method1             ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then c.method2             ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then c.method3             ");
			sbf.append(" 	end");
			sbf.append(" 	end");
			sbf.append("     ");
			sbf.append(" when g.systemid is not null     ");
			sbf.append(" then");
			sbf.append(" 	case when g.methodse='实收比例'");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then g.method1 * realtotal ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then g.method2 * realtotal ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then g.method3 * realtotal ");
			sbf.append(" 	end");
			sbf.append(" 	when g.methodse='应收比例'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then g.method1 * total     ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then g.method2 * total     ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then g.method3 * total     ");
			sbf.append(" 	end");
			sbf.append(" 	when g.methodse='固定金额'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then g.method1             ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then g.method2             ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then g.method3             ");
			sbf.append(" 	end");
			sbf.append(" 	end");
			sbf.append("     ");
			sbf.append("     ");
			sbf.append(" when d.systemid is not null     ");
			sbf.append(" then");
			sbf.append(" 	case when d.methodse='实收比例'");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then d.method1 * realtotal ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then d.method2 * realtotal ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then d.method3 * realtotal ");
			sbf.append(" 	end");
			sbf.append(" 	when d.methodse='应收比例'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then d.method1 * total     ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then d.method2 * total     ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then d.method3 * total     ");
			sbf.append(" 	end");
			sbf.append(" 	when d.methodse='固定金额'     ");
			sbf.append(" 	then             ");
			sbf.append(" 		case when method='指定'      ");
			sbf.append(" 			then d.method1             ");
			sbf.append(" 		when method='轮牌'           ");
			sbf.append(" 			then d.method2             ");
			sbf.append(" 		when method='开发'           ");
			sbf.append(" 			then d.method3             ");
			sbf.append(" 	end");
			sbf.append(" 	end");
			sbf.append("     ");
			sbf.append("     ");
			sbf.append("     ");
			sbf.append(" end  as assessrate");
			sbf.append("     ");
			sbf.append("  from             ");
			sbf.append(" (   ");
			sbf.append(" select bcode,method,serviceitem,total, realtotal from       ");
			sbf.append(" (   ");
			sbf.append(" select bcode800 as bcode ,method800 as method,serviceitem,total,realtotal,upicode from s_service_po_g ");
			sbf.append(" 	union all        ");
			sbf.append("     ");
			sbf.append(" select bcode801 as bcode,method801  as method,serviceitem,total,realtotal,upicode from s_service_po_g ");
			sbf.append(" 	union all        ");
			sbf.append(" select bcode802 as bcode,method802 as method ,serviceitem,case when bcode810 is null  then total  when bcode810 ='' then total else total/2 end as total,case when bcode810 is null  then realtotal  when bcode810 ='' then realtotal else realtotal/2 end as realtotal,upicode from s_service_po_g");
			sbf.append(" 	union all ");
			sbf.append("            ");
			sbf.append(" select bcode810 as bcode,method810 as method ,serviceitem,case when bcode802 is null  then total  when bcode802 ='' then total else total/2 end as total,case when bcode802 is null  then realtotal  when bcode802 ='' then realtotal else realtotal/2 end as realtotal,upicode from s_service_po_g");
			sbf.append(" 	union all ");
			sbf.append(" select bcode900 as bcode,method900 as method,serviceitem,total,realtotal,upicode from s_service_po_g         ");
			sbf.append(" ) as a     ");
			sbf.append(" where  exists            ");
			sbf.append(" (          ");
			sbf.append("            ");
			sbf.append(" select * from s_service_po where datepart(year,serviceenddate) ="+year.getSimpleValue()+" and datepart(month,serviceenddate)="+month.getSimpleValue()+" and a.upicode = s_service_po.icode           ");
			sbf.append(" )   and  exists ( select * from assessment_bcode_tbl_g where assessment_bcode_tbl_g.bcode = a.bcode and methodicode='按照比例绩效')       ");
			sbf.append(" and a.bcode is not null  ");
			sbf.append("            ");
			sbf.append(" ) as a     ");
			sbf.append("            ");
			sbf.append(" left join  ");
			sbf.append(" (          ");
			sbf.append(" select bcode,systemid,groupicode from bcode          ");
			sbf.append(" ) as b     ");
			sbf.append(" on a.bcode=b.bcode       ");
			sbf.append("            ");
			sbf.append(" left join  ");
			sbf.append(" (          ");
			sbf.append(" select systemid,bcode,service,max(method1) as method1,max(method2) as method2,   ");
			sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment  group by bcode,systemid,service,methodse  ");
			sbf.append(" ) as f     ");
			sbf.append(" on a.serviceitem =f.service and  b.systemid = f.systemid and a.bcode = f.bcode   ");
			sbf.append("            ");
			sbf.append("            ");
			sbf.append(" left join  ");
			sbf.append(" (          ");
			sbf.append(" select bcode,systemid,max(method1) as method1,max(method2) as method2,           ");
			sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where service is null or service='' group by bcode,systemid,methodse   ");
			sbf.append(" ) as c     ");
			sbf.append(" on a.bcode=c.bcode and b.systemid = c.systemid       ");
			sbf.append("            ");
			sbf.append("            ");
			sbf.append(" left join  ");
			sbf.append(" (          ");
			sbf.append(" select systemid, service,max(method1) as method1,max(method2) as method2,        ");
			sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where bcode is null or bcode='' group by service,systemid,methodse     ");
			sbf.append(" ) as e     ");
			sbf.append(" on a.serviceitem =e.service and  b.systemid = e.systemid           ");
			sbf.append("            ");
			sbf.append(" left join  ");
			sbf.append(" (          ");
			sbf.append(" select systemid,max(method1) as method1,max(method2) as method2,   ");
			sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where (bcode is null or bcode='') and (service is null or service='') group by systemid,methodse   ");
			sbf.append(" ) as d     ");
			sbf.append(" on b.systemid=d.systemid ");
			sbf.append("            ");
			sbf.append("            ");
			sbf.append(" left join  ");
			sbf.append(" (          ");
			sbf.append(" select systemid,groupicode,max(method1) as method1,max(method2) as method2,      ");
			sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where (bcode is null or bcode='') and (service is null or service='') group by systemid,groupicode,methodse      ");
			sbf.append(" ) as g     ");
			sbf.append(" on b.systemid=g.systemid and b.groupicode = g.groupicode           ");
			sbf.append("            ");
			sbf.append(" left join  ");
			sbf.append(" (          ");
			sbf.append(" select systemid,groupicode,service,max(method1) as method1,max(method2) as method2,            ");
			sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where (bcode is null or bcode='') group by systemid,groupicode,service,methodse      ");
			sbf.append(" ) as h     ");
			sbf.append(" on b.systemid=h.systemid and b.groupicode = h.groupicode and a.serviceitem = h.service         ");
			sbf.append("  union all select bcode, cast (paid* ( select top 1 method from assessment_rule2g where a.paid/area >1 order by a.paid/area asc  ");
			sbf.append(" ) as money) as assessrate from  ( select bcode ,sum(paid) paid from s_service_po  where exists ( select * from assessment_bcode_tbl_g where assessment_bcode_tbl_g.bcode = s_service_po.bcode and methodicode='按照区间绩效' )  ");
			sbf.append(" and datepart(year,serviceenddate) ="+year.getSimpleValue()+" and datepart(month,serviceenddate)="+month.getSimpleValue()+" group by bcode ) as a");
			sbf.append(" ) as z     ");
			sbf.append("            ");
			sbf.append(" group by bcode           ");
			return new String(sbf);
		}
}
